package com.blog.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import com.blog.dto.Article;
import com.blog.util.DataAccess;
import com.blog.util.DateTime;

public class ArticleDao {
	/**
	 * 添加文章，返回true或者false
	 * @param art
	 * @return
	 */
	public boolean addArticle(Article _art) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "insert into article values(null,?,?,?,?,?,?,?,?,?,?,?,?,null) ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, _art.getAuthor());
			ps.setString(2, _art.getTitle());
			ps.setString(3, _art.getDescription());
			ps.setString(4, _art.getKeyword());
			ps.setString(5, _art.getContent());
			ps.setString(6, _art.getImage());
			ps.setTimestamp(7, _art.getPublish_time());
			ps.setInt(8, _art.getReader());
			ps.setInt(9, _art.getReview());
			ps.setString(10, _art.getTag());
			ps.setString(11, _art.getCategory());
			ps.setInt(12, _art.getIscmd());
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 修改文章，返回true或者false
	 * @param _art
	 * @return
	 */
	public boolean editArticleById(Article _art) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "update article set author = ? ,title = ? ,description = ? ," + 
				"	keyword = ? ,content = ? ,image = ? ,publish_time = ? ," + 
				"	reader = ? ,review = ? , tag = ? ,category = ? ,iscmd = ?  " + 
				"	where  article_id = ? AND delete_at is NULL ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, _art.getAuthor());
			ps.setString(2, _art.getTitle());
			ps.setString(3, _art.getDescription());
			ps.setString(4, _art.getKeyword());
			ps.setString(5, _art.getContent());
			ps.setString(6, _art.getImage());
			ps.setTimestamp(7, _art.getPublish_time());
			ps.setInt(8, _art.getReader());
			ps.setInt(9, _art.getReview());
			ps.setString(10, _art.getTag());
			ps.setString(11, _art.getCategory());
			ps.setInt(12, _art.getIscmd());
			ps.setInt(13, _art.getArticle_id());
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!修改失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据文章id删除文章，返回true或者false
	 * @param _id
	 * @return
	 */
	public boolean delArticleById(int _id) {
		//删除article_tag的联系
		Article_tagDao article_tagDao = new Article_tagDao();
		article_tagDao.delArticle_tagByArtId(_id);
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		//删除文章
		String sql = "update article set delete_at = ? where  article_id = ? AND delete_at is NULL ";
		try {
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, DateTime.getDateTime());
			ps.setInt(2, _id);
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!软删除失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据用户id和文章id删除文章，返回true或者false
	 * @param _id
	 * @return
	 */
	public boolean delArticleById(int user_id ,int article_id) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;	
//		//获取要删除的文章内容
//		Article art=(findArticleById(article_id)).get(0);
//		//查找标签Id
//		TagDao tagDao = new TagDao();
//		String tags = art.getTag();
//		String[] tagArrays = tags.split(","); 
//		Vector<Integer> vTagId = new Vector<Integer>();
//		for(String tt:tagArrays) {
//			vTagId.add(tagDao.findTagByUser_idAndTag(tt, user_id));
//		}
		
		//删除article_tag的联系
		Article_tagDao article_tagDao = new Article_tagDao();
		article_tagDao.delArticle_tagByArtId(article_id);
		//删除文章
		String sql = "update article set delete_at = ? where  article_id = ? AND delete_at is NULL ";
		try {
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, DateTime.getDateTime());
			ps.setInt(2, user_id);
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!软删除失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	
	/**
	 * 查找所有的文章
	 * @return
	 */
	public Vector<Article> findAllArticle() {
		Vector<Article> v=new Vector<Article>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from article where delete_at  is  NULL order by article_id desc ";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Article article=new Article();
				article.setArticle_id(rs.getInt("article_id"));
				article.setAuthor(rs.getString("author"));
				article.setCategory(rs.getString("category"));
				article.setContent(rs.getString("content"));
				article.setDelete_at(rs.getTimestamp("delete_at"));
				article.setDescription(rs.getString("description"));
				article.setImage(rs.getString("image"));
				article.setIscmd(rs.getInt("iscmd"));
				article.setKeyword(rs.getString("keyword"));
				article.setPublish_time(rs.getTimestamp("publish_time"));
				article.setReader(rs.getInt("reader"));
				article.setReview(rs.getInt("review"));
				article.setTag(rs.getString("tag"));
				article.setTitle(rs.getString("title"));
				v.add(article);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	/**
	 * 通过文章id查找文章
	 * @param _id
	 * @return
	 */
	public Vector<Article> findArticleById(int _id) {
		Vector<Article> v=new Vector<Article>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from article where article_id = ? AND delete_at  is  NULL  order by article_id desc  ";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, _id);
			rs = ps.executeQuery();
			if(rs.next()) {
				Article article=new Article();
				article.setArticle_id(rs.getInt("article_id"));
				article.setAuthor(rs.getString("author"));
				article.setCategory(rs.getString("category"));
				article.setContent(rs.getString("content"));
				article.setDelete_at(rs.getTimestamp("delete_at"));
				article.setDescription(rs.getString("description"));
				article.setImage(rs.getString("image"));
				article.setIscmd(rs.getInt("iscmd"));
				article.setKeyword(rs.getString("keyword"));
				article.setPublish_time(rs.getTimestamp("publish_time"));
				article.setReader(rs.getInt("reader"));
				article.setReview(rs.getInt("review"));
				article.setTag(rs.getString("tag"));
				article.setTitle(rs.getString("title"));
				v.add(article);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
//	
//	String sql = "select count(*) from category c where 1 = 1   and c.category_name like ? ";   
//	pstmts   =   conn.prepareStatement(sql);   
//	pstmts.setString(1,"%资讯%");   
//	 preStmt.setString(1, "%"+content+"%");
//	rs   =   pstmts.executeQuery();  

	/**
	 * 根据文章的作者（用户名）查找文章
	 * @param _author
	 * @return
	 */
	public Vector<Article> findArticleByAuthor(String _author) {
		Vector<Article> v=new Vector<Article>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from article where author LIKE ? AND delete_at  is  NULL   order by article_id desc ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1,"%"+_author+"%");
			rs = ps.executeQuery();
			while (rs.next()) {
				Article article=new Article();
				article.setArticle_id(rs.getInt("article_id"));
				article.setAuthor(rs.getString("author"));
				article.setCategory(rs.getString("category"));
				article.setContent(rs.getString("content"));
				article.setDelete_at(rs.getTimestamp("delete_at"));
				article.setDescription(rs.getString("description"));
				article.setImage(rs.getString("image"));
				article.setIscmd(rs.getInt("iscmd"));
				article.setKeyword(rs.getString("keyword"));
				article.setPublish_time(rs.getTimestamp("publish_time"));
				article.setReader(rs.getInt("reader"));
				article.setReview(rs.getInt("review"));
				article.setTag(rs.getString("tag"));
				article.setTitle(rs.getString("title"));
				v.add(article);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	/**
	 * 根据文章的标题查找文章
	 * @param _title
	 * @return
	 */
	public Vector<Article> findArticleByTitle(String _title) {
		Vector<Article> v=new Vector<Article>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from article where title LIKE ? AND delete_at  is  NULL   order by article_id desc   ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1,"%"+_title+"%");
			rs = ps.executeQuery();
			while (rs.next()) {
				Article article=new Article();
				article.setArticle_id(rs.getInt("article_id"));
				article.setAuthor(rs.getString("author"));
				article.setCategory(rs.getString("category"));
				article.setContent(rs.getString("content"));
				article.setDelete_at(rs.getTimestamp("delete_at"));
				article.setDescription(rs.getString("description"));
				article.setImage(rs.getString("image"));
				article.setIscmd(rs.getInt("iscmd"));
				article.setKeyword(rs.getString("keyword"));
				article.setPublish_time(rs.getTimestamp("publish_time"));
				article.setReader(rs.getInt("reader"));
				article.setReview(rs.getInt("review"));
				article.setTag(rs.getString("tag"));
				article.setTitle(rs.getString("title"));
				v.add(article);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	/**
	 * 根据文章的关键字查找文章
	 * @param _keyword
	 * @return
	 */
	public Vector<Article> findArticleByKeyword(String _keyword) {
		Vector<Article> v=new Vector<Article>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from article where keyword LIKE ? AND delete_at  is  NULL   order by article_id desc   ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1,"%"+_keyword+"%");
			rs = ps.executeQuery();
			while (rs.next()) {
				Article article=new Article();
				article.setArticle_id(rs.getInt("article_id"));
				article.setAuthor(rs.getString("author"));
				article.setCategory(rs.getString("category"));
				article.setContent(rs.getString("content"));
				article.setDelete_at(rs.getTimestamp("delete_at"));
				article.setDescription(rs.getString("description"));
				article.setImage(rs.getString("image"));
				article.setIscmd(rs.getInt("iscmd"));
				article.setKeyword(rs.getString("keyword"));
				article.setPublish_time(rs.getTimestamp("publish_time"));
				article.setReader(rs.getInt("reader"));
				article.setReview(rs.getInt("review"));
				article.setTag(rs.getString("tag"));
				article.setTitle(rs.getString("title"));
				v.add(article);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	/**
	 * 根据文章的标签来查找文章
	 * @param _tag
	 * @return
	 */
	public Vector<Article> findArticleByTag(String _tag) {
		Vector<Article> v=new Vector<Article>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from article where tag LIKE ? AND delete_at  is  NULL   order by article_id desc   ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1,"%"+_tag+"%");
			rs = ps.executeQuery();
			while (rs.next()) {
				Article article=new Article();
				article.setArticle_id(rs.getInt("article_id"));
				article.setAuthor(rs.getString("author"));
				article.setCategory(rs.getString("category"));
				article.setContent(rs.getString("content"));
				article.setDelete_at(rs.getTimestamp("delete_at"));
				article.setDescription(rs.getString("description"));
				article.setImage(rs.getString("image"));
				article.setIscmd(rs.getInt("iscmd"));
				article.setKeyword(rs.getString("keyword"));
				article.setPublish_time(rs.getTimestamp("publish_time"));
				article.setReader(rs.getInt("reader"));
				article.setReview(rs.getInt("review"));
				article.setTag(rs.getString("tag"));
				article.setTitle(rs.getString("title"));
				v.add(article);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	/**
	 * 根据文章的内容来查找文章
	 * @param _content
	 * @return
	 */
	public Vector<Article> findArticleByContent(String _content) {
		Vector<Article> v=new Vector<Article>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from article where content LIKE ? AND delete_at  is  NULL   order by article_id desc   ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1,"%"+_content+"%");
			rs = ps.executeQuery();
			while (rs.next()) {
				Article article=new Article();
				article.setArticle_id(rs.getInt("article_id"));
				article.setAuthor(rs.getString("author"));
				article.setCategory(rs.getString("category"));
				article.setContent(rs.getString("content"));
				article.setDelete_at(rs.getTimestamp("delete_at"));
				article.setDescription(rs.getString("description"));
				article.setImage(rs.getString("image"));
				article.setIscmd(rs.getInt("iscmd"));
				article.setKeyword(rs.getString("keyword"));
				article.setPublish_time(rs.getTimestamp("publish_time"));
				article.setReader(rs.getInt("reader"));
				article.setReview(rs.getInt("review"));
				article.setTag(rs.getString("tag"));
				article.setTitle(rs.getString("title"));
				v.add(article);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	/**
	 * 根据分类这种文章
	 * @param _category
	 * @return
	 */
	public Vector<Article> findArticleByCategory(String _category) {
		Vector<Article> v=new Vector<Article>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from article where category LIKE ? AND delete_at  is  NULL   order by article_id desc   ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1,"%"+_category+"%");
			rs = ps.executeQuery();
			while (rs.next()) {
				Article article=new Article();
				article.setArticle_id(rs.getInt("article_id"));
				article.setAuthor(rs.getString("author"));
				article.setCategory(rs.getString("category"));
				article.setContent(rs.getString("content"));
				article.setDelete_at(rs.getTimestamp("delete_at"));
				article.setDescription(rs.getString("description"));
				article.setImage(rs.getString("image"));
				article.setIscmd(rs.getInt("iscmd"));
				article.setKeyword(rs.getString("keyword"));
				article.setPublish_time(rs.getTimestamp("publish_time"));
				article.setReader(rs.getInt("reader"));
				article.setReview(rs.getInt("review"));
				article.setTag(rs.getString("tag"));
				article.setTitle(rs.getString("title"));
				v.add(article);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	/**
	 * 根据用户的输入的字符串，在标题，描述，内容，标签，分类中查找相关的文章
	 * @param _str
	 * @return
	 */
	public Vector<Article> findArticleByString(String _str) {
		Vector<Article> v=new Vector<Article>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from article where ( title LIKE ? OR content LIKE ?  OR description LIKE ?   OR  tag LIKE ?  OR category LIKE ?  ) AND (delete_at  is  NULL)  order by article_id desc  ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1,"%"+_str+"%");
			ps.setString(2,"%"+_str+"%");
			ps.setString(3,"%"+_str+"%");
			ps.setString(4,"%"+_str+"%");
			ps.setString(5,"%"+_str+"%");
			rs = ps.executeQuery();
			while (rs.next()) {
				Article article=new Article();
				article.setArticle_id(rs.getInt("article_id"));
				article.setAuthor(rs.getString("author"));
				article.setCategory(rs.getString("category"));
				article.setContent(rs.getString("content"));
				article.setDelete_at(rs.getTimestamp("delete_at"));
				article.setDescription(rs.getString("description"));
				article.setImage(rs.getString("image"));
				article.setIscmd(rs.getInt("iscmd"));
				article.setKeyword(rs.getString("keyword"));
				article.setPublish_time(rs.getTimestamp("publish_time"));
				article.setReader(rs.getInt("reader"));
				article.setReview(rs.getInt("review"));
				article.setTag(rs.getString("tag"));
				article.setTitle(rs.getString("title"));
				v.add(article);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	
	/**
	 * 查找最后插入的文章ID
	 * @param _category
	 * @return
	 */
	public int findLastArticleId() {
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		int article_id = 0;
		String sql = " select * from article where article_id=(select max(article_id) from article)  ";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				article_id = rs.getInt("article_id");
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
			return 0;
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return article_id;
	}
	
	/**
	 * 根据分类这种文章
	 * @param _category
	 * @return
	 */
	public Vector<Article> findLastArticle() {
		Vector<Article> v=new Vector<Article>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from article where article_id=(select max(article_id) from article)  ";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Article article=new Article();
				article.setArticle_id(rs.getInt("article_id"));
				article.setAuthor(rs.getString("author"));
				article.setCategory(rs.getString("category"));
				article.setContent(rs.getString("content"));
				article.setDelete_at(rs.getTimestamp("delete_at"));
				article.setDescription(rs.getString("description"));
				article.setImage(rs.getString("image"));
				article.setIscmd(rs.getInt("iscmd"));
				article.setKeyword(rs.getString("keyword"));
				article.setPublish_time(rs.getTimestamp("publish_time"));
				article.setReader(rs.getInt("reader"));
				article.setReview(rs.getInt("review"));
				article.setTag(rs.getString("tag"));
				article.setTitle(rs.getString("title"));
				v.add(article);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
			return null;
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	
	
}
